/**
 * User数据模型模块
 */

//导入Node.js文件模块
const fs = require("fs");
//导入SQLite3模块   verbose() 方法会输出更详细的日志信息，例如 SQL 语句或执行结果等
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

class UserDB{
    /**
     * @constructor
     * @private
     */
    constructor(){
        this.dbFile = config.dbFile;
        this.instance = null; //UserDB数据库实例
        this.db = null; //SQLite3实例
        this.register=this.add;
    }

    /**
   * 创建UserDB对象
   * @returns {UserDB} UserDB实例
   */
    static getInstance(){
        if(!this.instance){
            this.instance = new UserDB();
        }
        return this.instance;
    }

    /**
 * 连接数据库
 * @returns {Promise}
 */
 connect(){
    return new Promise((resolve,reject) => {
        this.db = new sqlite3.Database(this.dbFile,(err) => {
            if(err){
                reject(err)
            }else{
                resolve("connect ok")
            }
        })
    })
}
/**
 * 关闭数据库
 * @returns {Promise}
 */
close(){
    return new Promise((resolve,reject) => {
        this.db.close((err) => {
            if(err){
                reject(err)
            }else{
                resolve("close ok")
            }
        })
    })
}

/**
 * 获取指定ID的用户信息
 * @param {Number} userId 用户ID
 * @returns {Promise}
 */
find(userId){
    return new Promise((resolve,reject) => {
        let sql = "select * from users where id=?";
        let params = [userId];
        this.db.get(sql,params,function(err,result) {
            if(err){
                reject(err);
            }else{
                resolve(result);
            }
        })
    })
}
/**
 * 获取用户信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns {Promise}
 */
findAll(limit = -1,offset = -1){
    return new Promise((resolve,reject) => {
        let sql = "";
        let params = [];
        if(limit === -1){
            sql = "select * from users order by id";
        }else if(offset === -1){
            sql = "select * from users order by id limit ?";
            params[0] = limit;
        }else{
            sql = "select * from users order by id limit ? offset ?";
            params[0] = limit;
            params[1] = offset;
        }
        // 一条用get，所有用all
        this.db.all(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(result);
            }
        })
    })
}
/**
 * 新增用户
 * @param {Object} user 用户数据
 * @returns {Promise}
 */
add(user){
    return new Promise((resolve,reject) => {
        let sql = `insert into users (
            username, password, nickname, truename, avatar,
            role, last_login_time, last_login_ip, created_time, created_ip, 
            updated_time
        ) values (
            ?, ?, ?, ?, ?, 
            ?, ?, ?, ?, ?, 
            ?
        );`;
        let params = [
                user.username,
                user.password,
                user.nickname,
                user.truename,
                user.avatar,
                user.role,
                user.lastLoginTime,
                user.lastLoginIP,
                user.createdTime,
                user.createdIP,
                user.updatedTime,];
        this.db.run(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                util.log(this)
                resolve(this.lastID); //插入的数据的自增ID
            }
        })
    })
}
/**
 * 修改用户
 * @param {Object} user 用户数据
 * @returns {Promise}
 */
update(user){
    return new Promise((resolve,reject) => {
        let sql = `update users set
        username = ?, password = ?, nickname = ?, truename = ?, 
        avatar = ?, role = ?, updated_time = ?
        WHERE id = ?
        ;`;
        let params = [user.username,
            user.password,
            user.nickname,
            user.truename,
            user.avatar,
            user.role,
            user.updatedTime,
            user.id,];
        this.db.run(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(this.changes); //影响的记录数
            }
        })
    })
}

/**
 * 删除用户
 * @param {Number} userId 用户ID
 * @returns {Promise}
 */
remove(userId){
    return new Promise((resolve,reject) => {
        let sql = "delete from users where id=?";
        let params = [userId];
        this.db.run(sql,params,function(err,result)  {
            if(err){
                reject(err);
            }else{
                resolve(this.changes);//影响的记录数
            }
        })
    })
}


/**
 * 登录
 * @param {String} username 用户名
 * @param {String} password 密码
 * @returns {Promise}
 */
login(username,password){
    return new Promise((resolve,reject) => {
        let sql = "select * from users where username=? and password=?";
        let params = [username,password];
        this.db.get(sql,params,function(err,result) {
            if(err){
                reject(err);
            }else{
                resolve(result);
            }
        })
    })
}
  /**
   * 更新用户登录相关数据
   * @param {String} username 用户名
   * @param {Object} lastLogin 登录状态对象 
   * @returns {Promise}
   */
touch(username,lastLogin){
    return new Promise((resolve,reject) => {
        let sql = `UPDATE users SET 
            last_login_time = ? , last_login_ip = ?, 
            login_count = login_count + 1 
            WHERE username = ?`;
        let params = [lastLogin.time, lastLogin.ip, username];
        this.db.run(sql,params,function(err,result) {
            if(err){
                reject(err);
            }else{
                resolve(result);
            }
        })
    })
}

/**
* 获取用户数量
* @returns {Promise}
*/
getCount() {
    return new Promise((resolve, reject) => {
        let sql = "SELECT count(1) as total  FROM users";
        let params = [];
        this.db.all(sql, params, function (err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
}

/**
   * 搜索用户
   * @param {String} q 查询关键字
   * @param {Number} limit 数量
   * @param {Number} offset 偏移
   * @returns {Promise} 
   */
 search(q, limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
        let sql = "";
        q = `%${q}%`;
        let params = [q, q, q];
        if (limit === -1) {
            sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ?";
        } else if (offset === -1) {
            sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ? LIMIT ?";
            params.push(limit);
        } else {
            sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ? LIMIT ? OFFSET ?";
            params.push(limit);
            params.push(offset);
        }
        this.db.all(sql, params, (err, result) => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
}

/**
   * 修改用户个人信息
   * @param {Object} user 用户数据
   * @returns {Promise}
   */
changeInfo(id, info) {
    return new Promise((resolve, reject) => {
        let sql = `UPDATE users SET nickname = ?, truename = ?, avatar = ? WHERE id = ?;`;
        let params = [
        info.nickname,
        info.truename,
        info.avatar,
        id,
        ];
    
        this.db.run(sql, params, function (err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.changes); //影响的记录数
            }
        });
    });
}

    /**
    * 修改用户密码
    * @param {Number} id 用户id
    * @param {String} password 用户密码
    * @returns {Promise}
    */
 changePassword(id, password) {
    return new Promise((resolve, reject) => {
        let sql = `UPDATE users SET password = ? WHERE id = ?;`;
        let params = [password, id];
    
        this.db.run(sql, params, function (err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.changes); //影响的记录数
            }
        });
    });
}

}
module.exports = UserDB;